import pymysql
from flask import Blueprint, render_template, request, redirect, url_for, session, jsonify
import json
from config import Config
# from flask_sqlalchemy import SQLAlchemy
# from sqlalchemy import and_
# from model.management import Management
# db = SQLAlchemy()
mg = Blueprint('management', __name__)
# 1
'''
返回后台页面
'''
@mg.route('/management')
def management():
    if session.get('power') == None:
        session['power'] = -404
    # 判断session中admin的状态是否为成功登录
    if session['power'] >= 1:
        # 返回后台页面
        # return render_template('management.html')
        return render_template('/admin/index.html')
    else:
        # session已经失效, 返回登陆页
        # return render_template('/login.html', tip="session已失效,请重新登录")
        return render_template('login.html', tip="没有权限请先登录,请重新登录")

'''
操作中心
'''
@mg.route('/management_welocme')
def management_welocme():
    if session.get('power') == None:
        session['power'] = -404
    # 判断session中admin的状态是否为成功登录
    if session['power'] >= 1:

        connect = pymysql.connect(host=Config.MYSQL_HOST, user=Config.MYSQL_USER, passwd="Ye7187jq", port=6666,
                                 database="job_data")
        cursor = connect.cursor()

        cursor.execute('select *  from `management`')
        management_alter = cursor.fetchall()
        connect.commit()

        cursor.execute('select *  from `job_province`')
        province = cursor.fetchall()
        connect.commit()


        return render_template('/management.html',management_alter=management_alter,province=province)
    else:
        # session已经失效, 返回登陆页
        # return render_template('/login.html', tip="session已失效,请重新登录")
        return render_template('login.html', tip="没有权限请先登录,请重新登录")

'''
后台修改url
'''
@mg.route('/setting_url', methods=['POST'])
def setting_url():
    # 更改爬虫url
    if request.method == 'POST':
        data = json.loads(request.data.decode())
        url = data['url']
        connect = pymysql.connect(host=Config.MYSQL_HOST, user=Config.MYSQL_USER, passwd="Ye7187jq", port=6666,
                                  database="job_data")
        cursor = connect.cursor()
        cursor.execute('update management set value = %s where key = "url"', (url,))
        connect.commit()
        # db.session.query(Management).filter(Management.key == "url").update({'value': url})
        # db.session.commit()
    return "success"


'''
后台修改范围
'''
@mg.route('/setting_range', methods=['POST'])
def setting_range():
    if request.method == 'POST':
        data = json.loads(request.data.decode())
        menderPower = data['menderPower']
        content = data['content']

        if int(menderPower) >= 2:
            connect = pymysql.connect(host=Config.MYSQL_HOST, user=Config.MYSQL_USER, passwd="Ye7187jq",
                                      port=6666,
                                      database="job_data")
            cursor = connect.cursor()
            cursor.execute("update  `management` set v = %s where k = 'w_p'", (content,))
            connect.commit()
            return "success"
        else:
            return "fail"


@mg.route('/setting_province', methods=['POST'])
def setting_province():
    if request.method == 'POST':
        data = json.loads(request.data.decode())
        menderPower = data['menderPower']
        content = data['content']

        if int(menderPower) >= 2:
            connect = pymysql.connect(host=Config.MYSQL_HOST, user=Config.MYSQL_USER, passwd="Ye7187jq",
                                      port=6666,
                                      database="job_data")
            cursor = connect.cursor()
            cursor.execute("update  `management` set v = %s where k = 'province'", (content,))
            connect.commit()
            return "success"
        else:
            return "fail"



'''
后台修改全部
'''
@mg.route('/alter_all01', methods=['POST'])
def alter_all01():
    # 更改爬虫url
    if request.method == 'POST':

        url01 = request.form.get('desc')
        time01 = request.form.get('time')
        ip = request.form.get('ip')
        get_ip = request.form.get('get_ip')
        end = request.form.get('end')
        start = request.form.get('start')

        menderPower = request.form.get('menderPower')
        if int(menderPower) >= 2:
            connect = pymysql.connect(host=Config.MYSQL_HOST, user=Config.MYSQL_USER, passwd="Ye7187jq",
                                      port=6666,
                                      database="job_data")
            cursor = connect.cursor()
            cursor.execute("update `management` set v = %s where k = 'base_url'", (url01,))
            connect.commit()

            cursor.execute("update `management` set v = %s where k = 'sleep'", (time01,))
            connect.commit()

            cursor.execute("update  `management` set v = %s where k = 'ip'", (ip,))
            connect.commit()

            cursor.execute("update  `management` set v = %s where k = 'ip_url'", (get_ip,))
            connect.commit()

            cursor.execute("update  `management` set v = %s where k = 'end'", (end,))
            connect.commit()

            cursor.execute("update  `management` set v = %s where k = 'start'", (start,))
            connect.commit()

            cursor.execute("select *  from `management`")
            management_alter = cursor.fetchall()
            connect.commit()

            cursor.execute("select *  from `job_province`")
            province = cursor.fetchall()
            connect.commit()

            # 提示 1 修改成功  0
            warn=1
            return render_template('/management.html',management_alter=management_alter, province=province,warn=warn)
        else:
            return render_template('login.html', tip="没有权限请先登录,请重新登录")



'''
后台修改页数
'''
@mg.route('/setting_page', methods=['POST'])
def setting_page():
    # 更改爬虫url
    if request.method == 'POST':
        data = json.loads(request.data.decode())
        page = data['page']
        connect = pymysql.connect(host=Config.MYSQL_HOST, user=Config.MYSQL_USER, passwd="Ye7187jq",
                                  port=6666,
                                  database="job_data")
        cursor = connect.cursor()
        cursor.execute("update  `management` set v = %s where k = 'page'", (page,))
        connect.commit()
    return "success"








'''
后台修改管理员密码
'''
@mg.route('/setting_password', methods=['POST'])
def setting_password():
    # 更改爬虫url
    if request.method == 'POST':
        data = json.loads(request.data.decode())
        password = data['password']
        connect = pymysql.connect(host=Config.MYSQL_HOST, user=Config.MYSQL_USER, passwd="Ye7187jq",
                                  port=6666,
                                  database="job_data")
        cursor = connect.cursor()
        cursor.execute("update  `management` set v = %s where k = 'password'", (password,))
        connect.commit()
    return "success"

'''
后台普通用户列表
'''
@mg.route('/member-list',methods=['POST','GET'])
def member_list():

    connect = pymysql.connect(host=Config.MYSQL_HOST, user=Config.MYSQL_USER, passwd=Config.MYSQL_PASSWD, port=Config.MYSQL_PORT,
                              database=Config.MYSQL_DATABASE)
    cursor = connect.cursor()

    select02 = """select count(*) from `user`
                             where power < 1
                             """
    cursor.execute(select02)
    # 元组套元组
    countPaging = cursor.fetchall()[0][0]
    connect.commit()
    if countPaging % 10 == 0:
        pagingAll = int(countPaging / 10)
    else:
        pagingAll = (int(countPaging / 10))+1




    if  request.form.get('paging_1') != None:
        paging01 = (int(request.form.get('paging_1'))-1)*10
        if int(request.form.get('paging_1')) == -1 :
            if countPaging%10==0:
                paging01 = (int(countPaging/10)-1)*10
            else:
                paging01 = (int(countPaging / 10))*10

    else:
        paging01 = 0

    paging02 = int(paging01/10)

    select01 = """select user_id,
                         user_name, 
                         power,
                         e_mail ,
                         education, 
                         work_experience, 
                         current_work ,
                         current_pay ,
                         foretell_work01, 
                         foretell_work02 , 
                         foretell_work03 from `user` 
                         where power < 1
                         limit %s,10
                         """
    cursor.execute(select01,(paging01))
    # 元组套元组
    userTuple = cursor.fetchall()
    connect.commit()


    select02 = """select count(*) from `user`
                         where power < 1
                         """
    cursor.execute(select02)
    # 元组套元组
    countPaging = cursor.fetchall()
    connect.commit()




    session["selectReturn"] = -404

    if len(userTuple)==0:
        pagingX=-404
    else:
        pagingX=0



    return render_template('admin/member-list.html',userTuple=userTuple,pagingX=pagingX,paging02=paging02,pagingAll=pagingAll)


'''
后台管理员列表
'''
@mg.route('/member-list-root',methods=['POST','GET'])
def member_list_root():
    connect = pymysql.connect(host=Config.MYSQL_HOST, user=Config.MYSQL_USER, passwd=Config.MYSQL_PASSWD, port=Config.MYSQL_PORT,
                              database=Config.MYSQL_DATABASE)
    cursor = connect.cursor()

    select02 = """select count(*) from `user`
                             where power > 0
                             """
    cursor.execute(select02)
    # 元组套元组
    countPaging = cursor.fetchall()[0][0]
    connect.commit()
    if countPaging % 10 == 0:
        pagingAll = int(countPaging / 10)
    else:
        pagingAll = (int(countPaging / 10))+1




    if  request.form.get('paging_1') != None:
        paging01 = (int(request.form.get('paging_1'))-1)*10
        if int(request.form.get('paging_1')) == -1 :
            if countPaging%10==0:
                paging01 = (int(countPaging/10)-1)*10
            else:
                paging01 = (int(countPaging / 10))*10

    else:
        paging01 = 0

    paging02 = int(paging01/10)

    select01 = """
               select user_id,user_name, power, e_mail ,education, work_experience,
               current_work ,current_pay ,foretell_work01,foretell_work02, foretell_work03 
               from `user` where power > 0  limit %s,10
                         """
    cursor.execute(select01,(paging01))
    # 元组套元组
    userTuple = cursor.fetchall()
    connect.commit()
    #   查找提示框    0 =出现  -404 隐藏
    session["selectReturn"] = -404

    if len(userTuple)==0:
        pagingX=-404
    else:
        pagingX=0

    return render_template('admin/member-list-root.html', userTuple=userTuple,pagingX=pagingX,paging02=paging02,pagingAll=pagingAll)




'''
冻结用户
'''
@mg.route('/stop_user', methods=['POST', 'GET'])
def stop_user():
    connect = pymysql.connect(host=Config.MYSQL_HOST, user=Config.MYSQL_USER, passwd=Config.MYSQL_PASSWD, port=Config.MYSQL_PORT,
                              database=Config.MYSQL_DATABASE)
    cursor = connect.cursor()
    data = json.loads(request.data.decode())
    # 修改者ID
    mender =  data['mender']
    # 修改ID
    user_id = data['user_id']
    if data['mender'] != user_id:
        # 获取账号
        cursor.execute("update `user` set power = -1  where user_id = %s", (user_id,))
        connect.commit()
        return "success"
    else:
        return "fail"

'''
开启用户
'''
@mg.route('/start_user', methods=['POST', 'GET'])
def start_user():
    connect = pymysql.connect(host=Config.MYSQL_HOST, user=Config.MYSQL_USER, passwd=Config.MYSQL_PASSWD, port=Config.MYSQL_PORT,
                              database=Config.MYSQL_DATABASE)
    cursor = connect.cursor()
    data = json.loads(request.data.decode())
    # 修改者ID
    mender =  data['mender']
    userPower = data['userPower']
    menderPower = data['menderPower']
    # 修改ID
    user_id = data['user_id']
    if userPower < menderPower and mender != user_id:
        # 获取账号
        cursor.execute("update `user` set power = 0  where user_id = %s", (user_id,))
        connect.commit()
        return "success"
    else:
        return "fail"
'''
编辑用户/修改权限
'''
@mg.route('/member-edit', methods=['POST', 'GET'])
def member_edit():
    connect = pymysql.connect(host=Config.MYSQL_HOST, user=Config.MYSQL_USER, passwd=Config.MYSQL_PASSWD, port=Config.MYSQL_PORT,
                              database=Config.MYSQL_DATABASE)
    cursor = connect.cursor()
    data = json.loads(request.data.decode())

    # 修改者ID
    mender = data['mender']
    user_id = str(data['user_id'])
    user_power = str(data['userPower'])
    mender_power = int(data['menderPower'])

    # 修改ID
    if mender_power >= 2 and mender != user_id:
        # 获取账号
        cursor.execute("update `user` set power = %s  where user_id = %s", (user_power, user_id))
        connect.commit()
        return "success"
    else:
        return "fail"

'''
查找用户
'''
@mg.route('/select_user', methods=['POST', 'GET'])
def select_user():
    connect = pymysql.connect(host=Config.MYSQL_HOST, user=Config.MYSQL_USER, passwd=Config.MYSQL_PASSWD, port=Config.MYSQL_PORT,
                              database=Config.MYSQL_DATABASE)
    cursor = connect.cursor()
    # 修改ID
    user_id = request.form.get('user_id')

    select01 = """
               select  user_id, user_name, power, e_mail, education, work_experience, 
               current_work, current_pay , foretell_work01, foretell_work02,foretell_work03  
               from `user`  where user_id = %s
               """
    cursor.execute(select01, (user_id,))
    connect.commit()
    user_tuple = cursor.fetchall()
    connect.commit()
    #   提示框    0 =出现  -404 隐藏
    session["selectReturn"] = 0
    pagingX=-404
    paging02=0
    pagingAll = 1
    return render_template('admin/member-list.html', userTuple=user_tuple,pagingX=pagingX,paging02=paging02,pagingAll=pagingAll)


'''
后台管理说明
'''
@mg.route('/admin_explain', methods=['POST', 'GET'])
def admin_explain():
    #   提示框    0 =出现  -404 隐藏
    session["selectReturn"] = 0
    return render_template('admin/admin_explain.html')



'''
后台可视化管理01
'''
@mg.route('/member_picture01', methods=['POST', 'GET'])
def member_picture01():
    #   提示框    0 =出现  -404 隐藏
    # session["selectReturn"] = 0
    connect = pymysql.connect(host=Config.MYSQL_HOST, user=Config.MYSQL_USER, passwd=Config.MYSQL_PASSWD, port=Config.MYSQL_PORT,
                              database=Config.MYSQL_DATABASE)
    cursor = connect.cursor()
    bar_data1 = []
    bar_data2 = []
    bar_legend = []
    cursor.execute('select distinct(now_date),user_count,sum_count from `user_monitor` order by now_date')
    rows1 = cursor.fetchall()[-7:]
    for row in rows1:
        bar_legend.append(str(row[0]))
        bar_data1.append(int(row[1]))
        bar_data2.append(row[2])

    cursor.execute('select education, count(*) from `user` group by education')
    rows2 = cursor.fetchall()
    pie_data = []
    for row in rows2:
        pie_data.append({'name': row[0], 'value': row[1]})

    return render_template('admin/member_picture01.html', pie_data=pie_data, bar_data1=bar_data1,
                           bar_data2=bar_data2, bar_legend=bar_legend)






'''
更新日志
'''
@mg.route('/system_log', methods=['POST', 'GET'])
def system_log():

    return render_template('admin/log.html')











